﻿using CoreRazor;
using Dapper;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CoreRp
{
    public class HelpDapper
    {
        public static HelpDapper Default { get; set; }
        public string ConnectionString { get; set; }
        public HelpDapper(string conn)
        {
            this.ConnectionString = conn;
        }
        public void Db(Action<OracleConnection> action)
        {
            using (OracleConnection connection = new OracleConnection(ConnectionString))
            {
                action(connection);
            }
        }
        public T Db<T>(Func<OracleConnection, T> action) where T : new()
        {
            using (OracleConnection connection = new OracleConnection(ConnectionString))
            {
                return action(connection);
            }
        }
        public M_PageView<T> Page<T>(string sql, object paras, int pageSize, long pageIndex, string[] desc = null, string[] asc = null) where T : new()
        {
            using (OracleConnection conn = new OracleConnection(ConnectionString))
            {
                if (desc == null && asc == null)
                {
                    throw new ArgumentNullException("desc和asc不能全为空");
                }
                string sqlTemplate = "SELECT * FROM ( SELECT pageview.*, ROW_NUMBER() OVER (ORDER BY {1}) AS row_num FROM ({0}) pageview ) pageview_1 WHERE pageview_1.row_num BETWEEN {2} AND {3} ORDER BY {1}";
                string sqlTemplateCount = "SELECT count(0) FROM ({0}) x ";

                StringBuilder sbAsc = new StringBuilder();
                if (desc != null)
                {
                    for (int i = 0; i < desc.Length; i++)
                    {

                        if (i == 0)
                        {
                            sbAsc.Append(desc[i]).Append(" DESC");
                        }
                        else
                        {
                            sbAsc.Append(",").Append(desc[i]).Append(" DESC");
                        }
                    }
                }
                if (asc != null)
                {
                    for (int i = 0; i < asc.Length; i++)
                    {

                        if (i == 0)
                        {
                            if (desc == null || desc.Length == 0)
                            {
                                sbAsc.Append(asc[i]).Append(" ASC ");
                            }
                            else
                            {
                                sbAsc.Append(",").Append(asc[i]).Append(" ASC ");
                            }
                        }
                        else
                        {
                            sbAsc.Append(",").Append(asc[i]).Append(" ASC ");
                        }
                    }
                }

                var sql_asc = sbAsc.ToString();
                var sql_start = "(:PAGESIZE*(:PAGEINDEX-1)+1)";
                var sql_end = ":PAGESIZE*:PAGEINDEX";
                var sql_count = string.Format(sqlTemplateCount, sql);
                string sql_query = string.Format(sqlTemplate, sql, sql_asc, sql_start, sql_end);

                var ptype = paras.GetType();
                var ps = paras.GetType().GetProperties();

                Dictionary<string, object> dicParas = new Dictionary<string, object>();
                foreach (var pi in ps)
                {
                    dicParas.Add(pi.Name, pi.GetValue(paras));
                }//aras,int pageSize,long pageIndex
                dicParas.Add("pageSize", pageSize);
                dicParas.Add("pageIndex", pageIndex);

                M_PageView<T> page = new M_PageView<T>();

                page.List = conn.Query<T>(sql_query, dicParas).ToList();
                page.Total = conn.Query<long>(sql_count, dicParas).First();

                return page;
            }
        }
    }
}